import pandas as pd

def load_excel_data(file_path):
    """加载并验证Excel数据"""
    df = pd.read_excel(file_path)
    
    # 定义固定列
    fixed_columns = ['题号', '班级得分率', '年级得分率', '班级年级得分率差', '答案', '作答情况']
    
    # 验证必要列
    missing_cols = [col for col in fixed_columns if col not in df.columns]
    if missing_cols:
        raise ValueError(f"缺少必要列: {missing_cols}")
    
    # 重构数据结构
    questions = []
    current_question = None
    
    for _, row in df.iterrows():
        # 情况1：题号和作答情况均为nan则跳过
        if pd.isna(row['题号']) and pd.isna(row['作答情况']):
            continue
            
        # 解析作答情况
        options = {}
        if not pd.isna(row['作答情况']):
            for item in str(row['作答情况']).split(';'):
                key, value = item.strip().split(':')
                options[key.strip()] = float(value.strip())
        
        # 情况2：题号为nan但作答情况不为nan
        if pd.isna(row['题号']) and current_question is not None:
            current_question['options'].update(options)
            continue
            
        # 正常情况：创建新question对象
        current_question = {
            **{col: row[col] for col in fixed_columns if col != '作答情况'},
            'options': options
        }
        questions.append(current_question)
    
    return {
        'questions': questions  # 移除 merged_ranges 字段
    }